﻿//2014-01-06
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.BasicDataManage
{
    public class ProductService : DbHelperBase
    {
        public int GetProductCount(ProductQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append("select  count(*) AS [count] FROM UT_Product AS A WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.PrdCode))
            {
                strSql.Append(" AND PrdCode like '%" + qcmodel.PrdCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdCode))
            {
                strSql.Append(" AND PrdName like '%" + qcmodel.PrdName + "%'");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<ProductEntity> GetProductList(ProductQcEntity qcmodel)
        {
            var list = new List<ProductEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,[PrdID],[PrdCode],[PrdName],[PrdDesc],[CheckFile],[OpFile],[RecStatus] FROM [UT_Product] ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.PrdCode))
            {
                strSql.Append(" AND PrdCode like '%" + qcmodel.PrdCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdName))
            {
                strSql.Append(" AND PrdName like '%" + qcmodel.PrdName + "%'");
            }
            strSql.Append(") AS T ");

            strSql.Append(" Where T.idx between " + start + " and " + end);

            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ProductEntity();
                            if (reader["PrdID"] != DBNull.Value)
                                model.PrdID = new Guid(reader["PrdID"].ToString());
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            model.CheckFile = reader["CheckFile"] == obj ? null : Convert.ToString(reader["CheckFile"]);
                            model.OpFile = reader["OpFile"] == obj ? null : Convert.ToString(reader["OpFile"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"].ToString());
                            model.PrdDesc = reader["PrdDesc"] == obj ? null : Convert.ToString(reader["PrdDesc"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<ProductEntity> GetProductListByCombobox()
        {
            var list = new List<ProductEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append("SELECT PrdID,PrdCode,PrdName FROM UT_Product where RecStatus=1");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ProductEntity();
                            if (reader["PrdID"] != DBNull.Value)
                                model.PrdID = new Guid(reader["PrdID"].ToString());
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<ProductEntity> GetProductListByQuery(string queryPrdCode)
        {
            var list = new List<ProductEntity>();
            string sql = "SELECT PrdID,PrdCode,PrdName FROM UT_Product where PrdCode Like @code AND RecStatus=1;";
            using (var cmd = Db.GetSqlStringCommond(sql))
            {
                Db.AddInParameter<string>(cmd, "@code", queryPrdCode + "%");
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ProductEntity();
                            model.PrdID = ReaderHelper.GetValue<Guid>(reader, "PrdID");
                            model.PrdCode = ReaderHelper.GetValue<string>(reader, "PrdCode");
                            model.PrdName = ReaderHelper.GetValue<string>(reader, "PrdName");
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool AddProduct(ProductEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [UT_Product]([PrdID],[PrdCode],[PrdName],[PrdDesc],[CheckFile],[OpFile],[RecStatus]) ");
            strSql.Append(" VALUES(@PrdID,@PrdCode,@PrdName,@PrdDesc,@CheckFile,@OpFile,@RecStatus) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", model.PrdID);
                    Db.AddInParameter(cmd, "@PrdCode", model.PrdCode);
                    Db.AddInParameter(cmd, "@PrdName", model.PrdName);
                    Db.AddInParameter(cmd, "@PrdDesc", model.PrdDesc);
                    Db.AddInParameter(cmd, "@CheckFile", model.CheckFile);
                    Db.AddInParameter(cmd, "@OpFile", model.OpFile);
                    Db.AddInParameter(cmd, "@RecStatus", model.RecStatus);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyProduct(ProductEntity model)
        {
            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [UT_Product] SET [PrdCode] = @PrdCode,[PrdName] = @PrdName,[PrdDesc] = @PrdDesc,[CheckFile] = @CheckFile,[OpFile] = @OpFile WHERE [PrdID] = @PrdID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdCode", model.PrdCode);
                    Db.AddInParameter(cmd, "@PrdName", model.PrdName);
                    Db.AddInParameter(cmd, "@PrdDesc", model.PrdDesc);
                    Db.AddInParameter(cmd, "@CheckFile", model.CheckFile);
                    Db.AddInParameter(cmd, "@OpFile", model.OpFile);
                    Db.AddInParameter(cmd, "@PrdID", model.PrdID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public ProductEntity GetProductInfo(string id)
        {
            var model = new ProductEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [PrdID],[PrdCode],[PrdName],[PrdDesc],[CheckFile],[OpFile],[RecStatus] FROM [UT_Product] Where PrdID=@PrdID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@PrdID", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["PrdID"] != DBNull.Value)
                                model.PrdID = new Guid(reader["PrdID"].ToString());
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            model.CheckFile = reader["CheckFile"] == obj ? null : Convert.ToString(reader["CheckFile"]);
                            model.OpFile = reader["OpFile"] == obj ? null : Convert.ToString(reader["OpFile"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"].ToString());
                            model.PrdDesc = reader["PrdDesc"] == obj ? null : Convert.ToString(reader["PrdDesc"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public Guid GetPrdId(string prdCode)
        {
            var prdId = new Guid();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [PrdID] FROM [UT_Product] Where PrdCode=@PrdCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@PrdCode", prdCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["PrdID"] != DBNull.Value)
                                prdId = new Guid(reader["PrdID"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return prdId;
            }
        }

        public void BatchFlow(List<FlowEntity> flowList)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapFlowId =
                    new SqlBulkCopyColumnMapping("FlowID", "FlowID");
                bcp.ColumnMappings.Add(mapFlowId);

                var mapPrdId =
                    new SqlBulkCopyColumnMapping("PrdID", "PrdID");
                bcp.ColumnMappings.Add(mapPrdId);

                var mapProcessIdx =
                    new SqlBulkCopyColumnMapping("ProcessIdx", "ProcessIdx");
                bcp.ColumnMappings.Add(mapProcessIdx);

                var mapModelMin =
                   new SqlBulkCopyColumnMapping("ModelMin", "ModelMin");
                bcp.ColumnMappings.Add(mapModelMin);

                bcp.DestinationTableName = "UT_Flow";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(flowList));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public void BatchFlowDoc(List<FlowDocEntity> flowDocList)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapDocID =
                    new SqlBulkCopyColumnMapping("DocID", "DocID");
                bcp.ColumnMappings.Add(mapDocID);

                var mapFlowID =
                    new SqlBulkCopyColumnMapping("FlowID", "FlowID");
                bcp.ColumnMappings.Add(mapFlowID);

                var mapPageNo =
                    new SqlBulkCopyColumnMapping("PageNo", "PageNo");
                bcp.ColumnMappings.Add(mapPageNo);

                var mapDocType =
                    new SqlBulkCopyColumnMapping("DocType", "DocType");
                bcp.ColumnMappings.Add(mapDocType);

                bcp.DestinationTableName = "UT_FlowDoc";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(flowDocList));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public int GetFlowListByIdCount(ProductQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(*) AS [count] from dbo.UT_Flow AS A  ");
            strSql.Append(" WHERE A.PrdID=@PrdID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<FlowEntity> GetFlowListById(ProductQcEntity qcmodel)
        {
            var list = new List<FlowEntity>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.FlowID,A.ProcessIdx,A.ModelMin from dbo.UT_Flow AS A  ");
            strSql.Append(" WHERE A.PrdID=@PrdID order by A.ProcessIdx ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new FlowEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = int.Parse(reader["ProcessIdx"].ToString());
                            if (reader["ModelMin"] != DBNull.Value)
                                model.ModelMin = Convert.ToDecimal(reader["ModelMin"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<FlowDocEntity> GetFlowDocListById()
        {
            var list = new List<FlowDocEntity>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT FlowID,PageNo,DocType from UT_FlowDoc ");
            strSql.Append(" order by PageNo ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new FlowDocEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["PageNo"] != DBNull.Value)
                                model.PageNo = Convert.ToInt32(reader["PageNo"]);
                            if (reader["DocType"] != DBNull.Value)
                                model.DocType = Convert.ToInt32(reader["DocType"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<FlowEntity> GetFlowInfo(ProductQcEntity qcmodel)
        {
            var list = new List<FlowEntity>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.FlowID,A.ProcessIdx,A.ModelMin from dbo.UT_Flow AS A ");
            strSql.Append(" WHERE A.PrdID=@PrdID ");
            strSql.Append(" order by A.ProcessIdx ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new FlowEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = int.Parse(reader["ProcessIdx"].ToString());
                            if (reader["ModelMin"] != DBNull.Value)
                                model.ModelMin = Convert.ToDecimal(reader["ModelMin"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<FlowDocEntity> GetFlowDocListByPrdId(string prdID)
        {
            var list = new List<FlowDocEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT B.FlowID,B.PageNo,B.DocType,A.ProcessIdx from UT_Flow AS A ");
            strSql.Append(" INNER JOIN  UT_FlowDoc AS B ON A.FlowID=B.FlowID ");
            strSql.Append(" WHERE A.PrdID=@PrdID ");
            strSql.Append(" order by B.PageNo ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", prdID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new FlowDocEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["PageNo"] != DBNull.Value)
                                model.PageNo = Convert.ToInt32(reader["PageNo"].ToString());
                            if (reader["DocType"] != DBNull.Value)
                                model.DocType = Convert.ToInt32(reader["DocType"].ToString());
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = Convert.ToInt32(reader["ProcessIdx"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool DeleteFlowDoc(Guid flowID)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_FlowDoc Where FlowID=@FlowID ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@FlowID", flowID);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool DeleteFlow(Guid prdID)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_Flow Where PrdID=@PrdID ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", prdID);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<FlowEntity> GetAllFlowList(Guid prdID)
        {
            var list = new List<FlowEntity>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT FlowID,ModelMin FROM UT_Flow WHERE PrdID=@PrdID Order by ProcessIdx ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@PrdID", prdID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new FlowEntity();
                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["ModelMin"] != DBNull.Value)
                                model.ModelMin = Convert.ToDecimal(reader["ModelMin"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public void ModifyFlow(List<FlowEntity> updatelist)
        {
            const string sqlStr = " UPDATE [UT_Flow] SET [ModelMin] = @ModelMin WHERE [FlowID] = @FlowID ";
            using (DbCommand cmd = Db.GetSqlStringCommond(sqlStr))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ModelMin", DbType.Decimal);
                    Db.AddInParameter(cmd, "@FlowID", DbType.Guid);
                    foreach (var i in updatelist)
                    {
                        cmd.Parameters["@ModelMin"].Value = i.ModelMin;
                        cmd.Parameters["@FlowID"].Value = i.FlowID;
                        Db.ExecuteNonQuery(cmd);
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
        }
    }
}
